The modulo (%) operator returns the remainder of one number divided by another. The syntax of the modulo (%) operator is as follows:
<dividend> % <divisor>
The <dividend> is the numeric expression to divide and it must be a valid expression of any one of the data types in the integer and monetary data type categories, or of the numeric data type. The <divisor> is the numeric expression to divide the dividend by and must be a valid expression of any one of the data types in the integer and monetary data type categories, or of the numeric data type.
Here are a few examples on the uses of the modulo (%) operator:
Usage #1 : Generate a Random Number
SELECT ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 100 + 1 AS [RandomNumber]
Usage #2 : Determine if a Number is Odd or Even
DECLARE @Input INT
SET @Input = ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 100 + 1
SELECT @Input AS [Input], CASE WHEN @Input % 2 = 0 THEN 'Even' ELSE 'Odd' END
Usage #3 : Determine if a Date/Year is a Leap Year
DECLARE @Year INT = YEAR(GETDATE())
SELECT CASE WHEN (@Year % 4 = 0 AND @Year % 100 != 0) OR @Year % 400 = 0
THEN 'Leap Year'
ELSE 'Not a Leap Year'
END AS [IsLeapYear]
Usage #4 : Determine if a Number is a Palindrome
DECLARE @Input INT = 123454321
DECLARE @ReversedInput INT = 0
DECLARE @TempInput INT
SET @TempInput = @Input
WHILE @TempInput > 0
BEGIN
SET @ReversedInput = @ReversedInput * 10 + @TempInput % 10
SET @TempInput = @TempInput / 10
END
SELECT @Input AS [Input], CASE WHEN @Input = @ReversedInput
THEN 'Palindrome'
ELSE 'Not a Palindrome' END AS [Palindrome]
Usage #5 : Get the Greatest Common Factor (GCF)
DECLARE @Dividend INT = 1989
DECLARE @Divisor INT = 867
DECLARE @Remainder INT = -1
WHILE @Remainder != 0
BEGIN
SET @Remainder = @Dividend % @Divisor
SET @Dividend = @Divisor
SET @Divisor = @Remainder
END
SELECT @Dividend AS [GCF]
GCF
-----
51
Usage #6 : Determine if a Number is a Prime Number
DECLARE @Input INT
DECLARE @IsPrime BIT = 1
DECLARE @Factor INT = 2
SET @Input = ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 100 + 3
WHILE @Factor <= SQRT(@Input)
BEGIN
IF @Input % @Factor = 0
BEGIN
SET @IsPrime = 0
BREAK
END
SET @Factor = @Factor + 1
END
SELECT @Input AS [Input], @IsPrime AS [IsPrime]
Usage #7 : Determine if a Number is Divisible by Another Number
DECLARE @Dividend INT
DECLARE @Divisor INT
SET @Dividend = ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 1000 + 100
SET @Divisor = ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 10 + 1
SELECT @Dividend AS [Dividend], @Divisor AS [Divisor],
CASE WHEN @Dividend % @Divisor = 0
THEN 'Divisible'
ELSE 'Not Divisible'
END